﻿Imports System
Imports System.IO
Imports System.Data.SqlServerCe
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Common

Module CouponDao

    Public Function findEngineerCoupons(ByVal projectId As String, ByVal couponDate As String, ByVal couponNo As String, ByVal status As String, ByVal couponId As String, ByVal barcode As String, ByVal couponInOut As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT coupon.COUPON_ID, coupon.COUPON_CODE, coupon.COUPON_TYPE, coupon.WELDER_USER_ID, coupon.COAX, coupon.REMARKS " & _
              " , coupon.PROJECT_ID, coupon.STATUS_CODE, project.PROJECT_NAME, coupon.JOINT_TYPE_NAME_1, coupon.JOINT_TYPE_NAME_2, coupon.BARCODE " & _
              " , coupon.MATERIAL, coupon.SIZE, coupon.THICKNESS, u.USER_NAME, coupon.DATE_ADD, coupon.TIME_ADD " & _
              " , coupon.IS_COAX, coupon.PIPE_TUBE, coupon.HEAT_NUMBER_ID_1, coupon.HEAT_NUMBER_NAME_1, coupon.HEAT_NUMBER_ID_2, coupon.HEAT_NUMBER_NAME_2, coupon.WELD_MACHINE_ID, coupon.WELD_MACHINE_NAME, weld.WELD_COUNT " & _
              " FROM eqam_coupon coupon LEFT JOIN eqam_project project ON coupon.PROJECT_ID = project.PROJECT_ID " & _
              " LEFT JOIN eqam_user u ON u.USER_ID = coupon.WELDER_USER_ID " & _
              " LEFT JOIN (SELECT count(*) AS WELD_COUNT, COUPON_CODE FROM eqam_weldlog_detail GROUP BY COUPON_CODE) weld ON weld.COUPON_CODE = coupon.COUPON_CODE " & _
              " WHERE 1=1 "

        If (projectId IsNot Nothing And projectId <> "") Then
            SQL = SQL & " AND coupon.PROJECT_ID = @projectId"
            paramList.Add("@projectId", projectId)
        End If

        If (couponId IsNot Nothing And couponId <> "") Then
            SQL = SQL & " AND coupon.COUPON_ID = @couponId"
            paramList.Add("@couponId", couponId)
        End If

        If (couponInOut IsNot Nothing And couponInOut <> "") Then
            SQL = SQL & " AND coupon.COUPON_TYPE = @COUPON_TYPE"
            paramList.Add("@COUPON_TYPE", couponInOut)
        End If

        If (couponDate IsNot Nothing And couponDate <> "") Then
            SQL = SQL & " AND coupon.DATE_ADD = @DATE_ADD"
            paramList.Add("@DATE_ADD", couponDate)
        End If

        If (couponNo IsNot Nothing And couponNo <> "") Then
            SQL = SQL & " AND coupon.COUPON_CODE = @COUPON_CODE"
            paramList.Add("@COUPON_CODE", couponNo)
        End If

        If (status IsNot Nothing And status <> "") Then
            SQL = SQL & " AND coupon.STATUS_CODE = @STATUS_CODE"
            paramList.Add("@STATUS_CODE", status)
        End If

        If (barcode IsNot Nothing And barcode <> "") Then
            SQL = SQL & " AND coupon.BARCODE = @BARCODE"
            paramList.Add("@BARCODE", barcode)
        End If
        SQL = SQL & " ORDER BY coupon.DATE_ADD, coupon.TIME_ADD DESC"

        Return LoadData(SQL, paramList)
    End Function

    Public Function getCouponCodeByBarcode(ByVal barcode As String) As String
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim result As Boolean = False
        Dim counponCode As String = ""

        Dim SQL As String = "SELECT COUPON_CODE FROM eqam_coupon WHERE BARCODE = @BARCODE"

        paramList.Add("@BARCODE", barcode)

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            counponCode = dtResult.Rows(0).Item("COUPON_CODE")
        End If

        Return counponCode
    End Function

    Function getCoupon(ByVal couponId As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT * FROM eqam_coupon WHERE COUPON_ID = @COUPON_ID"
        paramList.Add("@COUPON_ID", couponId)

        Return LoadData(SQL, paramList)
    End Function

    Public Function findGroupBySizes(ByVal projectId As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT SIZE FROM eqam_coupon WHERE PROJECT_ID = @PROJECT_ID GROUP BY SIZE ORDER BY SIZE "

        paramList.Add("@PROJECT_ID", projectId)

        Return LoadData(SQL, paramList)
    End Function

    Function findCouponNoByGroup() As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT COUPON_CODE FROM eqam_coupon ORDER BY COUPON_CODE "

        Return LoadData(SQL, paramList)
    End Function

    Function findCouponCreateDateByGroup() As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT DATE_ADD FROM eqam_coupon GROUP BY DATE_ADD ORDER BY DATE_ADD DESC"

        Return LoadData(SQL, paramList)
    End Function

    Public Function findGroupByThickness(ByVal projectId As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        
        Dim SQL As String = "SELECT THICKNESS FROM eqam_coupon WHERE PROJECT_ID = @PROJECT_ID GROUP BY THICKNESS ORDER BY THICKNESS "

        paramList.Add("@PROJECT_ID", projectId)

        Return LoadData(SQL, paramList)
    End Function

    Public Function checkBarcodeDuplication(ByVal barcode As String, ByVal ignoreCouponId As String) As Boolean
        Dim result As Boolean = False

        If (result = False) Then
            result = checkBarcodeDuplicationForCoupon(barcode, ignoreCouponId)
        End If
        If (result = False) Then
            result = checkBarcodeDuplicationForWeldlogDetail(barcode, Nothing)
        End If
        Return result
    End Function

    Public Function checkBarcodeDuplicationForCoupon(ByVal barcode As String, ByVal ignoreCouponId As String) As Boolean
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim result As Boolean = False

        Dim SQL As String = "SELECT BARCODE FROM eqam_coupon WHERE BARCODE = @BARCODE"

        paramList.Add("@BARCODE", barcode)

        If (ignoreCouponId IsNot Nothing And ignoreCouponId <> "") Then
            SQL = SQL & "  AND COUPON_ID <> @COUPON_ID"
            paramList.Add("@COUPON_ID", ignoreCouponId)
        End If

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            result = True
        End If

        Return result
    End Function

    Public Function doCreateCoupon(ByVal userId As String, ByVal coax As String, ByVal projectId As String, ByVal material As String, ByVal size As String, ByVal thickness As String, ByVal isCoax As String, ByVal pipeTube As String, ByVal couponType As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal lv As Windows.Forms.ListView, ByVal parentCouponBarcode As String, ByVal barcode As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String, ByVal weldMachineId As String, ByVal weldMachineName As String) As Boolean
        Dim conn As New SqlCeConnection()
        Dim cmd As SqlCeCommand
        Dim sqlCeTransaction As SqlCeTransaction
        Dim SQL As String = ""
        Dim success As Boolean

        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            Dim paramList As New Hashtable()

            Dim couponCode As String = ""
            If (couponType = Globals.COUPON_TYPE_IN) Then
                couponCode = getCouponCode(projectId)
            Else
                couponCode = _COUPON_CODE
            End If

            SQL = "INSERT INTO eqam_coupon(COUPON_CODE, ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, STATUS_CODE" & _
                          " ,COUPON_TYPE, WELDER_USER_ID, PIPE_TUBE, IS_COAX" & _
                          " ,COAX, PROJECT_ID, MATERIAL, SIZE, THICKNESS, REMARKS" & _
                          " ,HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2, PARENT_COUPON_BARCODE, BARCODE" & _
                          " ,JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2, WELD_MACHINE_ID, WELD_MACHINE_NAME, PDA_CODE)" & _
                          " VALUES (@COUPON_CODE, @ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY, @STATUS_CODE" & _
                          " ,@COUPON_TYPE, @WELDER_USER_ID, @PIPE_TUBE, @IS_COAX " & _
                          " ,@COAX, @PROJECT_ID, @MATERIAL, @SIZE, @THICKNESS, @REMARKS" & _
                          " ,@HEAT_NUMBER_ID_1, @HEAT_NUMBER_NAME_1, @HEAT_NUMBER_ID_2, @HEAT_NUMBER_NAME_2, @PARENT_COUPON_BARCODE, @BARCODE" & _
                          " ,@JOINT_TYPE_CODE_1, @JOINT_TYPE_NAME_1, @JOINT_TYPE_CODE_2, @JOINT_TYPE_NAME_2, @WELD_MACHINE_ID, @WELD_MACHINE_NAME, @PDA_CODE)"

            Dim currentDate As Decimal = PdaUtils.getCurrentDate
            Dim currentTime As Decimal = PdaUtils.getCurrentTime
            'Dim tubeTypeId As String = Me.getTubeTypeId(material, size, thickness)

            'first line
            paramList.Add("@COUPON_CODE", couponCode)
            paramList.Add("@ADD_BY", _USER_ID)
            paramList.Add("@DATE_ADD", currentDate)
            paramList.Add("@TIME_ADD", currentTime)
            paramList.Add("@DATE_UPDATE", currentDate)
            paramList.Add("@TIME_UPDATE", currentTime)
            paramList.Add("@UPDATE_BY", _USER_ID)
            paramList.Add("@STATUS_CODE", Globals.COUPON_STATUS_PENDING)
            'sec line
            paramList.Add("@COUPON_TYPE", couponType)
            paramList.Add("@WELDER_USER_ID", _USER_ID)
            paramList.Add("@PIPE_TUBE", pipeTube)
            paramList.Add("@IS_COAX", isCoax)
            'third line
            paramList.Add("@COAX", coax)
            paramList.Add("@PROJECT_ID", projectId)
            paramList.Add("@MATERIAL", material)
            paramList.Add("@SIZE", size)
            paramList.Add("@THICKNESS", thickness)
            paramList.Add("@REMARKS", "")

            paramList.Add("@HEAT_NUMBER_ID_1", heatId1)
            paramList.Add("@HEAT_NUMBER_NAME_1", heatName1)
            paramList.Add("@HEAT_NUMBER_ID_2", heatId2)
            paramList.Add("@HEAT_NUMBER_NAME_2", heatName2)
            paramList.Add("@PARENT_COUPON_BARCODE", parentCouponBarcode)
            paramList.Add("@BARCODE", barcode)

            paramList.Add("@JOINT_TYPE_CODE_1", jointTypeCode1)
            paramList.Add("@JOINT_TYPE_NAME_1", jointTypeName1)
            paramList.Add("@JOINT_TYPE_CODE_2", jointTypeCode2)
            paramList.Add("@JOINT_TYPE_NAME_2", jointTypeName2)
            paramList.Add("@WELD_MACHINE_ID", weldMachineId)
            paramList.Add("@WELD_MACHINE_NAME", weldMachineName)

            paramList.Add("@PDA_CODE", _PDA_CODE)

            sqlCeTransaction = conn.BeginTransaction
            cmd = conn.CreateCommand
            cmd.CommandText = SQL

            For Each de As DictionaryEntry In paramList
                cmd.Parameters.AddWithValue(de.Key, de.Value)
            Next de

            'cmd.CommandTimeout = 1800
            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()

            ''''''''''''''''''''''''''''''''''''''''''''               
            cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
            Dim couponId As Integer = cmd.ExecuteScalar

            '''''''''''''''''''''''''''''''''''''''''''''
            If (couponType = Globals.COUPON_TYPE_IN) Then
                If (lv IsNot Nothing) Then
                    ' save coupon system
                    Dim lstItem As ListViewItem
                    For Each lstItem In lv.Items
                        If (lstItem.Checked) Then
                            Dim systemId As String = lstItem.Tag

                            Dim sqlCouponSystem As String = "INSERT INTO eqam_coupon_system " & _
                            " (COUPON_BARCODE, SYSTEM_TYPE_ID, PDA_CODE, ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY)" & _
                            " VALUES (@COUPON_BARCODE, @SYSTEM_TYPE_ID, @PDA_CODE, @ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY)"

                            cmd = conn.CreateCommand
                            cmd.CommandText = sqlCouponSystem

                            paramList = New Hashtable()
                            paramList.Add("@COUPON_BARCODE", barcode)
                            paramList.Add("@SYSTEM_TYPE_ID", systemId)
                            paramList.Add("@PDA_CODE", _PDA_CODE)
                            paramList.Add("@ADD_BY", _USER_ID)
                            paramList.Add("@DATE_ADD", currentDate)
                            paramList.Add("@TIME_ADD", currentTime)
                            paramList.Add("@DATE_UPDATE", currentDate)
                            paramList.Add("@TIME_UPDATE", currentTime)
                            paramList.Add("@UPDATE_BY", _USER_ID)

                            'MsgBox(systemId & "-" & sqlCouponSystem)                            
                            For Each de As DictionaryEntry In paramList
                                cmd.Parameters.AddWithValue(de.Key, de.Value)
                            Next de

                            'cmd.CommandTimeout = 1800
                            cmd.Transaction = sqlCeTransaction
                            cmd.ExecuteNonQuery()
                        End If
                    Next
                End If
            Else
                ' update coupon (in) to complete
                cmd = conn.CreateCommand
                cmd.CommandText = "UPDATE eqam_coupon SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                            ", STATUS_CODE = @STATUS_CODE" & _
                            " WHERE COUPON_ID = @COUPON_ID"

                paramList = New Hashtable()
                ' 1st line
                paramList.Add("@DATE_UPDATE", currentDate)
                paramList.Add("@TIME_UPDATE", currentTime)
                paramList.Add("@UPDATE_BY", _USER_ID)

                ' 2nd line
                paramList.Add("@STATUS_CODE", Globals.COUPON_STATUS_COMPLETED)
                paramList.Add("@COUPON_ID", _COUPON_ID)

                'MsgBox(systemId & "-" & sqlCouponSystem)
                For Each de As DictionaryEntry In paramList
                    cmd.Parameters.AddWithValue(de.Key, de.Value)
                Next de

                'cmd.CommandTimeout = 1800
                cmd.Transaction = sqlCeTransaction
                cmd.ExecuteNonQuery()
            End If

            _SAVE_SUCCESSFUL_MESSAGE = "Coupon " & couponCode & " created."

            sqlCeTransaction.Commit()
            success = True
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, sysname & " - ex")
            MsgBox("LOCAL CONNECTION CREATE COUPON: " & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - ExecuteSQL")
            sqlCeTransaction.Rollback()
            success = False
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try
        Return success
    End Function

    Public Function getCouponCode(ByVal projectId As String) As String
        'coupon id = projc-welderid-050512-01
        Dim couponCode As String
        Dim couponCount As Integer = getTodayCouponCount(projectId)
        Dim rightNow As Date = Now
        couponCode = _PROJECT_NAME & "-" & _USER_NAME & "-" & rightNow.ToString("ddMMyy") & "-" & (couponCount + 1)

        Return couponCode
    End Function

    Public Function getTodayCouponCount(ByVal projectId As String) As Integer
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim couponCount As Integer = 0
        
        Dim dateAdd As String = PdaUtils.getCurrentDate
        Dim SQL As String = "SELECT count(*) AS _COUNT FROM eqam_coupon WHERE DATE_ADD = @DATE_ADD AND PROJECT_ID = @PROJECT_ID"
        paramList.Add("@DATE_ADD", dateAdd)
        paramList.Add("@PROJECT_ID", projectId)

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            Dim dataRow As DataRow = dtResult.Rows(0)
            couponCount = dataRow.Item("_COUNT")
        End If

        Return couponCount
    End Function

    Public Function checkCouponAllOut(ByVal projectId As String, ByVal welderUserId As String) As Boolean
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim result As Boolean = False

        Dim SQL As String = "SELECT COUPON_CODE FROM eqam_coupon WHERE WELDER_USER_ID = @WELDER_USER_ID AND PROJECT_ID = @PROJECT_ID AND STATUS_CODE IN (@STATUS_CODE_PEND, @STATUS_CODE_APPR) AND COUPON_TYPE = @COUPON_TYPE"
        paramList.Add("@WELDER_USER_ID", welderUserId)
        paramList.Add("@PROJECT_ID", projectId)
        paramList.Add("@STATUS_CODE_APPR", Globals.COUPON_STATUS_APPROVED)
        paramList.Add("@STATUS_CODE_PEND", Globals.COUPON_STATUS_PENDING)
        paramList.Add("@COUPON_TYPE", Globals.COUPON_TYPE_IN)

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            result = False
        Else
            result = True
        End If

        Return result
    End Function

    Public Function updateCouponStatus(ByVal remark As String, ByVal rejectReason As String, ByVal status As String, ByVal couponId As String) As Boolean
        Dim paramList As New Hashtable()

        Dim sql As String = "UPDATE eqam_coupon SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, STATUS_CODE = @STATUS_CODE, REMARKS = @REMARKS, REJECT_REASON = @REJECT_REASON" & _
                          " WHERE COUPON_ID = @COUPON_ID"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime

        paramList.Add("@DATE_UPDATE", currentDate)
        paramList.Add("@TIME_UPDATE", currentTime)
        paramList.Add("@UPDATE_BY", _USER_ID)
        paramList.Add("@STATUS_CODE", status)
        paramList.Add("@COUPON_ID", couponId)
        paramList.Add("@REMARKS", remark)
        paramList.Add("@REJECT_REASON", rejectReason)

        Dim success As Boolean = ExecuteSQL(sql, paramList)

        If (success = True) Then
            _SAVE_SUCCESSFUL_MESSAGE = "Update Successfully."
        End If
        Return success
    End Function
End Module
